from datetime import date
from datetime import datetime

from flask import current_app
from sqlalchemy import text, desc, and_, extract, or_

from db.models import IndustrySectorFund, IndustryInfo, StockInfo, StockMarket, StockFund, News, StockSpiderMark, \
    IndustryGrowthList, StockPool, StockHolders, MarketWorkDay, StockFundsHolder, ConceptRankingList, \
    ConceptStockPool, StockPositionRecord, StockPosition, StockOrder, StockAccount, \
    StockLifeline, LifelineDetail, StockOptional, HoldersRecord
from db.orm import save, queryAll, queryOneByFilter, saveList, updateList, queryOder, queryListByFilter, \
    saveOrUpdate, removeList, updateByParam, queryOderTOP, queryByJoin, queryJoin, queryBySql, \
    get_count, saveOrNot, fetch_to_dict, removeByParam
from utils.date_util import date2str
from utils.util import ObjDictTool, PinyinTool


class IndustryInfoDao:
    '''
    行业信息
    '''

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 100, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('name'):
            search = "%{}%".format(args.get('name'))
            param.append(IndustryInfo.name.like(search))

        query_filter = IndustryInfo.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))
        return query_filter.paginate(page=pageNo,
                                     per_page=pageSize,
                                     error_out=True)

    def saveOrUpdate(self, industry_info):
        current_app.logger.info("添加数据========================")
        code = getattr(industry_info, 'code')
        name = getattr(industry_info, 'name')
        if not code:
            current_app.logger.info('industry_name={}的编码为空'.format(name))
            return
        filtrs = [IndustryInfo.name == name]

        setattr(industry_info, 'create_time', datetime.now())
        setattr(industry_info, 'update_time', datetime.now())
        saveOrUpdate(IndustryInfo, industry_info, *filtrs)

    def findAll(self):
        return queryAll(IndustryInfo)

    def findByIndustryName(self, industry_name):
        filtrs = [IndustryInfo.name == industry_name]
        return queryOneByFilter(IndustryInfo, *filtrs)


class IndustrySectorFundsDao:
    '''
    行业板块信息
    '''

    def save(self, sector_fund):
        '''
        保存板块信息
        :param item:
        :return:
        '''
        industry_code = getattr(sector_fund, 'industry_code')
        current_app.logger.info("保存板块数据开始========={}===============".format(industry_code))
        create_time = getattr(sector_fund, 'create_time')
        if not create_time:
            current_app.logger.info('industry_code={}的创建时间为空'.format(industry_code))
            return

        filtrs = [IndustrySectorFund.industry_code == industry_code,
                  IndustrySectorFund.create_time == create_time]

        saveOrUpdate(IndustrySectorFund, sector_fund, *filtrs)

    def findByDateOrderBy(self, date):
        filtrs = [IndustrySectorFund.create_time == date]

        return queryOder(IndustrySectorFund, *filtrs, orderSql=IndustrySectorFund.main_net_inflow.desc())

    def findByDateOrderByTOP(self, date, limit):
        filtrs = [IndustrySectorFund.create_time == date]
        return queryOderTOP(IndustrySectorFund, *filtrs, orderSql=IndustrySectorFund.main_net_inflow.desc(), top=limit)

    def findTOPByDate(self, date):
        filtrs = [IndustrySectorFund.create_time >= date]
        return queryOder(IndustrySectorFund, *filtrs, orderSql=IndustrySectorFund.main_net_inflow.desc())

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('date'):
            param.append(IndustrySectorFund.create_time == args.get('date'))

        query_filter = IndustrySectorFund.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))
        return query_filter.paginate(page=pageNo,
                                     per_page=pageSize,
                                     error_out=True)

    def updateByIndustryName(self, industry):
        param = [IndustrySectorFund.create_time == getattr(industry, 'create_time'),
                 IndustrySectorFund.industry_name == getattr(industry, 'industry_name')]
        target = {'stock_imported': getattr(industry, 'stock_imported')}
        updateByParam(IndustrySectorFund, *param, **target)

    def updateById(self, industry):
        param = [IndustrySectorFund.id == getattr(industry, 'id')]
        target = {'stock_imported': str(getattr(industry, 'stock_imported'))}
        updateByParam(IndustrySectorFund, *param, **target)


class StockInfoDao:
    '''
    股票基础信息
    '''

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('industry_name'):
            param.append(StockInfo.industry_name == args.get('industry_name'))
        if args.get('abridge'):
            param.append(StockInfo.abridge == args.get('abridge'))
        if args.get('market'):
            param.append(StockInfo.market == args.get('market'))
        if args.get('stock_name'):
            param.append(StockInfo.stock_name == args.get('stock_name'))

        query_filter = StockInfo.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))
        return query_filter.paginate(page=pageNo,
                                     per_page=pageSize,
                                     error_out=True)

    def saveOrUpdate(self, stock_info):
        '''
        保存-股票信息(相同的不做处理)
        :param item:
        :return:
        '''
        stock_code = getattr(stock_info, "stock_code")
        current_app.logger.info("添加股票基础数据{}========================".format(ObjDictTool.to_dic(stock_info)))
        stock_name = getattr(stock_info, "stock_name")

        if not stock_code:
            current_app.logger.info('stock_name={}的编码为空'.format(stock_name))
            return
        filtrs = [StockInfo.stock_code == stock_code]
        setattr(stock_info, 'abridge', PinyinTool.getPinyinAbridge(stock_name))
        saveOrUpdate(StockInfo, stock_info, *filtrs)

    def findAll(self):
        return queryAll(StockInfo)

    def findByIndustry(self, industry):
        filtrs = [StockInfo.industry_name == industry]
        return queryListByFilter(StockInfo, *filtrs)

    def findByStockCode(self, stock_code):
        '''
        查询单个股票信息
        :param stock_code:
        :return:
        '''
        filtrs = [StockInfo.stock_code == stock_code]
        return queryOneByFilter(StockInfo, *filtrs)

    def findByStockCodeBatch(self, stock_code_list):
        '''
        批量查询股票信息
        :param stock_code_list:
        :return:
        '''
        filtrs = [StockInfo.stock_code.in_(stock_code_list)]
        return queryListByFilter(StockInfo, *filtrs)

    def saveList(self, stockList):
        current_app.logger.info("添加数据========================")
        find_all = self.findAll()
        stockJson = {}

        if find_all:
            for stock in find_all:
                stockJson[getattr(stock, 'stock_code')] = getattr(stock, 'id')

        saveStockList = []
        updateStockList = []

        for stock in stockList:
            try:
                stock_name = getattr(stock, 'stock_name')
                if 'st' in stock_name or 'ST' in stock_name:
                    continue
                stock_code = getattr(stock, 'stock_code')
                stock_id = stockJson.get(stock_code)
                setattr(stock, 'abridge', PinyinTool.getPinyinAbridge(stock_name))
                if stock_id:
                    setattr(stock, 'id', stock_id)
                    updateStockList.append(ObjDictTool.to_dic(stock))
                else:
                    saveStockList.append(stock)
            except:
                current_app.logger.error('异常,名称:{}'.format(PinyinTool.getPinyinAbridge(getattr(stock, 'stock_name'))))

        saveList(saveStockList)
        updateList(StockInfo, updateStockList)

    def removeStockBatch(self, ids):
        removeList(StockInfo, ids)

    def findNoSpiderStocks(self, create_date, stock_funds_1, stock_funds_2):
        filtrs = [or_(StockSpiderMark.trading_date == None, StockSpiderMark.trading_date == create_date)]
        if stock_funds_1:
            filtrs.append(StockSpiderMark.stock_funds_1 == None)
        if stock_funds_2:
            filtrs.append(StockSpiderMark.stock_funds_2 == None)
        return queryJoin(StockInfo, StockSpiderMark, StockInfo.stock_code == StockSpiderMark.stock_code, *filtrs)


class StockMarketDao:
    '''
    股票市场信息
    '''

    def queryByDate(self, date):
        filters = [StockMarket.create_time == date]
        return queryListByFilter(StockMarket, *filters)

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('date'):
            param.append(StockMarket.create_time == args.get('date'))

        query_filter = StockMarket.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def saveOrUpdate(self, market):
        '''
        保存板块-股票信息
        :param item:
        :return:
        '''
        current_app.logger.info("添加数据========================")
        create_time = getattr(market, 'create_time')
        if not create_time:
            current_app.logger.info('marketCode={}的创建时间为空'.format(getattr(market, 'market_code')))
            return
        filtrs = [StockMarket.market_code == getattr(market, 'market_code'), StockMarket.create_time == create_time]

        saveOrUpdate(StockMarket, market, *filtrs)


class StockFundsDao:
    '''
    股票涨幅和资金流入
    '''

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('date'):
            param.append(StockFund.create_time == args.get('date'))

        query_filter = StockFund.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def findBySql(self, industry_name, create_date, limit):

        filtrs = [StockFund.create_time == create_date]
        if industry_name:
            filtrs.append(StockInfo.industry_name == industry_name)
        list = queryByJoin(StockFund, StockInfo, StockFund.stock_code == StockInfo.stock_code,
                           desc(StockFund.main_net_inflow), limit, *filtrs)
        return list

    def saveOrUpdate(self, fund):
        '''
        保存板块-股票信息
        :param item:
        :return:
        '''
        current_app.logger.info("添加数据==========={}=============".format(getattr(fund, 'stock_code')))
        create_time = getattr(fund, "create_time")
        if not create_time:
            return
        filtrs = [StockFund.stock_code == getattr(fund, 'stock_code'), StockFund.create_time == create_time]
        setattr(fund, 'week_day', datetime.strptime(create_time, '%Y-%m-%d').date().weekday() + 1)
        saveOrUpdate(StockFund, fund, *filtrs)
        #     往股票爬虫标识表里插入数据
        mark = StockSpiderMark()
        setattr(mark, 'trading_date', create_time)
        setattr(mark, 'stock_code', getattr(fund, 'stock_code'))
        if getattr(fund, 'concentration_70'):
            setattr(mark, 'stock_funds_1', 1)
            StockSpiderMarkDao().saveOrUpdate(mark)
        if getattr(fund, 'open'):
            setattr(mark, 'stock_funds_2', 1)
            StockSpiderMarkDao().saveOrUpdate(mark)

    def saveList(self, stockList):
        current_app.logger.info("添加数据========================")

        find_all = queryAll(StockFund)
        stockJson = {}

        if find_all:
            for stockFunds in find_all:
                stock_code = getattr(stockFunds, 'stock_code')
                create_time = getattr(stockFunds, 'create_time')
                stockJson[stock_code + '-' + create_time.strftime('%Y-%m-%d')] = getattr(stockFunds, 'id')

        saveStockList = []
        updateStockList = []

        for stock in stockList:
            stock_name = getattr(stock, 'stock_name')
            if 'st' in stock_name or 'ST' in stock_name:
                continue
            stock_code = getattr(stock, 'stock_code')
            create_time = getattr(stock, 'create_time')
            stock_id = stockJson.get(stock_code + '-' + create_time)

            setattr(stock, 'week_day', datetime.strptime(create_time, '%Y-%m-%d').date().isoweekday())
            if stock_id:
                setattr(stock, 'id', stock_id)
                updateStockList.append(ObjDictTool.to_dic(stock))
            else:
                saveStockList.append(stock)

        saveList(saveStockList)
        updateList(StockFund, updateStockList)


class StockFundsHolderDao:
    def save(self, stockHolder):
        current_app.logger.info("添加数据========================")
        stock_code = getattr(stockHolder, "stock_code")
        create_time = getattr(stockHolder, "create_time")
        if not stock_code:
            return
        filtrs = [StockFundsHolder.stock_code == stock_code, StockFundsHolder.create_time == create_time]
        saveOrNot(StockFundsHolder, stockHolder, *filtrs)


class StockHoldersDao:
    def saveOrUpdate(self, stockHolder):
        id = getattr(stockHolder, "id")

        filtrs = [StockHolders.id == id]
        saveOrUpdate(StockHolders, stockHolder, *filtrs)

    def findAll(self):
        return queryAll(StockHolders)

    def queryByStockCode(self,stock_code):
        filters = [StockHolders.stock_code == stock_code]
        return queryOneByFilter(StockHolders, *filters)
    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 10, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('date'):
            param.append(StockHolders.create_time == args.get('date'))

        query_filter = StockHolders.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def removeById(self,holder_id):
        filtrs = [StockHolders.id == holder_id]
        removeByParam(StockHolders, *filtrs)


class StockFundamentalDao:
    def save(self, fundamental):
        current_app.logger.info("添加数据========================")

        save(fundamental)


class NewsDao:
    def saveOrUpdate(self, news):
        news_id = getattr(news, "news_id")
        if not news_id:
            return
        filtrs = [News.news_id == news_id]
        setattr(news, 'create_time', datetime.now())
        saveOrUpdate(StockFund, news, *filtrs)

    def updateById(self, news):
        param = [News.id == getattr(news, 'id')]
        target = {'keys': str(getattr(news, 'keys')),
                  'concept': getattr(news, 'concept'),
                  'industry': getattr(news, 'industry'),
                  'stock_code': getattr(news, 'stock_code')

                  }
        updateByParam(News, *param, **target)

    def removeBatch(self, ids):
        if ids:
            removeList(News, ids.split(','))

    def findByKeysNone(self):
        filtrs = [News.keys == None]
        return queryListByFilter(News, *filtrs)

    def findAll(self):
        return queryAll(News)

    def findById(self, news_id):
        filtrs = [News.id == news_id]
        return queryOneByFilter(News, *filtrs)

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('date'):
            query_date = args.get('date')
            query_date_arr = query_date.split('-')

            param.append(and_(
                extract('year', News.publish_time) == query_date_arr[0],
                extract('month', News.publish_time) == query_date_arr[1],
                extract('day', News.publish_time) == query_date_arr[2]))

        query_filter = News.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)


class StockSpiderMarkDao:
    def saveOrUpdate(self, mark):
        '''
        保存爬虫标识
        :param item:
        :return:
        '''
        current_app.logger.info("添加数据==========={}=============".format(getattr(mark, 'stock_code')))
        trading_date = getattr(mark, "trading_date")
        if not trading_date:
            return
        filtrs = [StockSpiderMark.stock_code == getattr(mark, 'stock_code'),
                  StockSpiderMark.trading_date == trading_date]
        setattr(mark, 'update_time', datetime.now())
        saveOrUpdate(StockSpiderMark, mark, *filtrs)


class IndustryGrowthListDao:
    def saveOrUpdate(self, industryGrowthList):
        save(industryGrowthList)
        # pass

    def findAll(self):
        return queryAll(IndustryGrowthList)

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('date'):
            query_date = args.get('date')
            query_date_arr = query_date.split('-')

            param.append(and_(
                extract('year', IndustryGrowthList.create_time) == query_date_arr[0],
                extract('month', IndustryGrowthList.create_time) == query_date_arr[1],
                extract('day', IndustryGrowthList.create_time) == query_date_arr[2]))

        query_filter = IndustryGrowthList.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)


class IndustryStockListDao:
    def saveOrUpdate(self, industryStockList):
        save(industryStockList)

        stock_pool = StockPool()
        ObjDictTool.copyA2B(industryStockList, stock_pool)

        current_app.logger.info(
            "保存股票池数据==========industryStockList:{}=============".format(ObjDictTool.to_dic(industryStockList)))
        current_app.logger.info("保存股票池数据==========stock_pool:{}=============".format(ObjDictTool.to_dic(stock_pool)))
        setattr(stock_pool, 'optional_state', 0)
        StockPoolDao().saveOrUpdate(stock_pool)


class StockPoolDao:
    def saveOrUpdate(self, stock_pool):
        stock_code = getattr(stock_pool, "stock_code")
        if not stock_code:
            return
        filtrs = [StockPool.stock_code == stock_code]
        setattr(stock_pool, 'update_time', datetime.now())
        saveOrUpdate(StockPool, stock_pool, *filtrs)

    def removeStockPoolBatch(self, ids):
        removeList(StockPool, ids)

    def queryPoolById(self, id):
        filtrs = [StockPool.id == id]
        return queryOneByFilter(StockPool, *filtrs)

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        # 排序字段
        sort = args.get('sort')
        # 正排/倒排
        order = args.get('order')
        industry_code = args.get("industry_code")

        param = []

        if args.get('date'):
            query_date = args.get('date')
            query_date_arr = query_date.split('-')

            param.append(and_(
                extract('year', StockPool.create_time) == query_date_arr[0],
                extract('month', StockPool.create_time) == query_date_arr[1],
                extract('day', StockPool.create_time) == query_date_arr[2]))

        if industry_code:
            param.append(StockPool.industry_code == industry_code)

        query_filter = StockPool.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def findAll(self):
        return queryAll(StockPool)

    def queryPoolIndustry(self):
        statement = text("""
                        SELECT DISTINCT
                            ( industry_code ),
                            industry_name 
                        FROM
                            `stock_pool`
                        """)
        param = {}
        query_by_sql = queryBySql(statement, **param)
        return query_by_sql


class MarketWorkDayDao:
    def saveOrUpdate(self, workDay):
        create_date = getattr(workDay, "create_date")
        if not create_date:
            return
        filtrs = [MarketWorkDay.create_date == create_date, MarketWorkDay.is_workday != getattr(workDay, "is_workday")]
        saveOrUpdate(MarketWorkDay, workDay, *filtrs)

    def judgeWorkDay(self, q_date):
        '''
        判断指定日期是否开市
        :param q_date: 查询日期
        :return: true/false
        '''
        statement = text("""
                               SELECT
                                    count(1) as count
                                FROM
                                    `market_workday` 
                                WHERE
                                    create_date =:query_date
                                    AND is_workday = 1 
                    """)
        param = {"query_date": q_date}
        count = get_count(sql=statement, params=param)
        return False if count == 0 else True

    def queryLastWorkDay(self, q_date, limit=1):
        '''
        查询最近的工作日(开市日)
        :param q_date: 查询的日期
        :return: 返回最近的工作日
        '''
        if not limit or limit < 1:
            return None
        statement = text("""
                               SELECT
                                    * 
                                FROM
                                    `market_workday` 
                                WHERE
                                    create_date <=:query_date
                                    AND is_workday = 1 
                                ORDER BY
                                    create_date DESC 
                                LIMIT :limit
                            """)
        param = {"query_date": q_date, 'limit': limit}

        if limit == 1:
            to_dict = fetch_to_dict(statement, param, 'one')
            if to_dict:
                return to_dict.get('create_date')
            return date.today()
        else:
            all_list = fetch_to_dict(statement, param)
            if all_list:
                return [date2str(obj.get('create_date')) for obj in all_list]
            return [date.today()]

    def findAll(self):
        return queryAll(MarketWorkDay)


class ConceptRankingListDao:
    def saveOrUpdate(self, conceptRankingList):
        save(conceptRankingList)
        # pass

    def findAll(self):
        return queryAll(ConceptRankingList)

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        sort = args.get('sort')
        order = args.get('order')
        param = []
        if args.get('date'):
            query_date = args.get('date')
            query_date_arr = query_date.split('-')

            param.append(and_(
                extract('year', ConceptRankingList.create_time) == query_date_arr[0],
                extract('month', ConceptRankingList.create_time) == query_date_arr[1],
                extract('day', ConceptRankingList.create_time) == query_date_arr[2]))

        query_filter = ConceptRankingList.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def findByDateOrderByTOP(self, date, limit):
        statement = text("""
                                SELECT
                                    *
                                FROM
                                    `concept_ranking_list` 
                                WHERE
                                    DATE(create_time) =:q_date
                                    AND time_interval = 60
                                LIMIT :limit
                        """)
        param = {'q_date': date, 'limit': limit}
        to_dict = fetch_to_dict(statement, param)

        return to_dict

    def findTOPByDate(self, date):
        filtrs = [ConceptRankingList.create_date >= date, ConceptRankingList.time_interval == '60']
        return queryOder(ConceptRankingList, *filtrs, orderSql=ConceptRankingList.main_net_inflow.desc())


class ConceptStockListDao:
    def saveOrUpdate(self, conceptStockList):
        save(conceptStockList)

        stock_pool = ConceptStockPool()
        ObjDictTool.copyA2B(conceptStockList, stock_pool)

        current_app.logger.info(
            "保存股票池数据==========industryStockList:{}=============".format(ObjDictTool.to_dic(conceptStockList)))
        current_app.logger.info("保存股票池数据==========stock_pool:{}=============".format(ObjDictTool.to_dic(stock_pool)))
        ConceptStockPoolDao().saveOrUpdate(stock_pool)


class ConceptStockPoolDao:
    def saveOrUpdate(self, stock_pool):
        stock_code = getattr(stock_pool, "stock_code")
        if not stock_code:
            return
        filtrs = [ConceptStockPool.stock_code == stock_code]
        setattr(stock_pool, 'update_time', datetime.now())
        saveOrUpdate(ConceptStockPool, stock_pool, *filtrs)

    def removeStockPoolBatch(self, ids):
        removeList(ConceptStockPool, ids)

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        # 排序字段
        sort = args.get('sort')
        # 正排/倒排
        order = args.get('order')
        concept_code = args.get("concept_code")

        param = []

        if args.get('date'):
            query_date = args.get('date')
            query_date_arr = query_date.split('-')

            param.append(and_(
                extract('year', ConceptStockPool.create_time) == query_date_arr[0],
                extract('month', ConceptStockPool.create_time) == query_date_arr[1],
                extract('day', ConceptStockPool.create_time) == query_date_arr[2]))

        if concept_code:
            param.append(ConceptStockPool.concept_code == concept_code)

        query_filter = ConceptStockPool.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def findAll(self):
        return queryAll(ConceptStockPool)

    def queryPoolIndustry(self):
        statement = text("""
                        SELECT DISTINCT
                            ( concept_code ),
                            concept_name 
                        FROM
                            `concept_stock_pool`
                        """)
        param = {}
        query_by_sql = queryBySql(statement, **param)
        return query_by_sql


class PositionRecordDao:
    def save(self, positionRecord):
        setattr(positionRecord, 'create_time', datetime.now())
        save(positionRecord)

    def queryByParam(self, position_id, order_id, stock_code):

        param = []

        if position_id:
            param.append(StockPositionRecord.position_id == position_id)

        if order_id:
            param.append(StockPositionRecord.order_id == order_id)
        if stock_code:
            param.append(StockPositionRecord.stock_code == stock_code)

        return queryListByFilter(StockPositionRecord, *param)

    def findAll(self):
        return queryAll(StockPositionRecord)


class PositionDao:
    def save(self, position):
        stock_code = getattr(position, "stock_code")
        if not stock_code:
            return
        filtrs = [StockPosition.stock_code == stock_code]
        setattr(position, 'update_time', datetime.now())
        saveOrUpdate(StockPosition, position, *filtrs)

    def queryByParam(self, position_id, stock_code):

        param = []

        if position_id:
            param.append(StockPosition.position_id == position_id)

        if stock_code:
            param.append(StockPosition.stock_code == stock_code)

        return queryListByFilter(StockPosition, *param)

    def findAll(self):
        return queryAll(StockPosition)

    def removeByPositionId(self, position_id):
        filtrs = [StockPosition.position_id == position_id]
        removeByParam(StockPosition, *filtrs)


class OrderDao:
    def save(self, order):
        order_id = getattr(order, "order_id")
        if not order_id:
            return
        filtrs = [StockOrder.order_id == order_id]
        setattr(order, 'update_time', datetime.now())
        saveOrUpdate(StockOrder, order, *filtrs)

    def queryByParam(self, order_id, stock_code, order_state):

        param = []

        if order_id:
            param.append(StockOrder.order_id == order_id)

        if stock_code:
            param.append(StockOrder.stock_code == stock_code)

        if order_state:
            param.append(StockOrder.order_state == order_state)

        return queryListByFilter(StockOrder, *param)

    def queryEffectiveOrder(self, stock_code):
        '''
        查询股票有效（新建，部分完成状态）的订单
        :param stock_code:
        :return:
        '''
        if not stock_code:
            return None
        filtrs = [StockOrder.stock_code == stock_code, or_(StockOrder.order_state == 0, StockOrder.order_state == 1)]
        return queryListByFilter(StockOrder, *filtrs)

    def findAll(self):
        return queryAll(StockOrder)


class AccountDao:
    def save(self, account):
        id = getattr(account, "id")
        if not id:
            return
        filtrs = [StockAccount.id == id]
        setattr(account, 'update_time', datetime.now())
        saveOrUpdate(StockAccount, account, *filtrs)

    def queryOne(self):
        return queryAll(StockAccount)[0]

    def findAll(self):
        return queryAll(StockAccount)


class StockLifelineDao:
    '''
    支撑线
    '''

    def save(self, stock_lifeline):
        id = getattr(stock_lifeline, "id")

        filtrs = [StockLifeline.id == id]
        setattr(stock_lifeline, 'update_time', datetime.now())
        saveOrUpdate(StockLifeline, stock_lifeline, *filtrs)

    def removeById(self, lifeline_id):
        filtrs = [StockLifeline.id == lifeline_id]
        removeByParam(StockLifeline, *filtrs)


    def queryLinesByPrice(self, stock_code, cur_price):
        if cur_price:
            lower_price = self.queryLowerByPrice(stock_code, cur_price)
            upper_price = self.queryUpperByPrice(stock_code, cur_price)
            return [lower_price, upper_price]
        else:
            return None

    def queryAllLinesByStock(self, args):

        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 10, type=int)
        # 排序字段
        sort = args.get('sort')
        # 正排/倒排
        order = args.get('order')
        stock_code = args.get("stock_code")

        param = []

        if stock_code:
            param.append(StockLifeline.stock_code == stock_code)

        query_filter = StockLifeline.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def queryLowerByPrice(self, stock_code, cur_price):
        statement = text("""
                                SELECT
                                    id,
                                    point_1_time,
                                    point_1_price 
                                FROM
                                    `stock_lifeline` 
                                WHERE
                                    stock_code =:stock_code
                                    AND point_1_price <:cur_price
                                    ORDER BY point_1_price DESC 
                                    LIMIT 1
                                  """)
        param = {'stock_code': stock_code, 'cur_price': cur_price}
        return fetch_to_dict(statement, param, fecth='one')

    def queryUpperByPrice(self, stock_code, cur_price):
        statement = text("""
                                   SELECT
                                       id,
                                       point_1_time,
                                       point_1_price 
                                   FROM
                                       `stock_lifeline` 
                                   WHERE
                                       stock_code =:stock_code
                                       AND point_1_price >:cur_price
                                       LIMIT 1
                                     """)
        param = {'stock_code': stock_code, 'cur_price': cur_price}

        return fetch_to_dict(statement, param, fecth='one')

    def saveList(self, lineList):
        if lineList:
            saveList(lineList)

    def queryAllStocks(self):
        sql = text("""
        SELECT stock_code,stock_name FROM `stock_lifeline` group by stock_code,stock_name
        """)
        param = {}
        return fetch_to_dict(sql, param)


class LifelineDetailDao:
    def save(self, life_detail):
        id = getattr(life_detail, "id")

        filtrs = [LifelineDetail.id == id]
        setattr(life_detail, 'update_time', datetime.now())
        saveOrUpdate(LifelineDetail, life_detail, *filtrs)

    def queryByLineCode(self, line_code):
        if not line_code:
            return None
        filtrs = [LifelineDetail.line_code == line_code]
        return queryListByFilter(LifelineDetail, *filtrs)


class StockOptionalDao:
    def saveOrUpdate(self, stock_optional):

        stock_code = getattr(stock_optional, "stock_code")
        if not stock_code:
            return
        filtrs = [StockOptional.stock_code == stock_code]
        setattr(stock_optional, 'update_time', datetime.now())
        saveOrUpdate(StockOptional, stock_optional, *filtrs)

    def queryOptionalById(self, optional_id):
        filtrs = [StockOptional.id == optional_id]
        return queryOneByFilter(StockOptional, *filtrs)

    def removeOptional(self, stock_code):
        filtrs = [StockOptional.stock_code == stock_code]
        removeByParam(StockOptional, *filtrs)

    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        # 排序字段
        sort = args.get('sort')
        # 正排/倒排
        order = args.get('order')
        industry_code = args.get("industry_code")

        param = []

        if industry_code:
            param.append(StockOptional.industry_code == industry_code)

        query_filter = StockOptional.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def findAll(self):
        return queryAll(StockOptional)


class HoldersRecordDao:
    def save(self, holder_record):
        setattr(holder_record, 'create_time', datetime.now())
        save(holder_record)

    def queryHoldersByCode(self, holder_code):
        filtrs = [HoldersRecord.holder_code == holder_code]
        return queryListByFilter(HoldersRecord, *filtrs)


    def queryByPage(self, args):
        pageNo = args.get('page', 1, type=int)
        pageSize = args.get('rows', 1, type=int)
        # 排序字段
        sort = args.get('sort')
        # 正排/倒排
        order = args.get('order')
        stock_code = args.get("stock_code")

        param = []

        if stock_code:
            param.append(HoldersRecord.stock_code == stock_code)

        query_filter = HoldersRecord.query.filter(*param)
        if sort:
            query_filter = query_filter.order_by(text(sort + ' ' + order))

        return query_filter.paginate(page=pageNo, per_page=pageSize,
                                     error_out=True)

    def findAll(self):
        return queryAll(HoldersRecord)

